<?php

	/**
	 * Defines a class to be used for Oracle databases
	 * 
	 * @author Pablo Santiago Sánchez <phackwer@gmail.com>
	 * @copyright Copyright (c) 2008, Pablo Santiago Sánchez
	 * @license http://opensource.org/licenses/bsd-license.php BSD License
	 * @package pop
	 * @subpackage drivers
	 */

	/**
	 * Defines a class to be used for OCI databases
	 * 
	 * @package pop
	 * @subpackage drivers
	 */
	class POCIDBDriver extends PGenericDBDriver
	{
		public $schema = 'public';
		public $separator = '.';
		public $driver = 'oci';
		
		/**
		 * Filter the field for update
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterUpdate($value, $type, $defaultval = null)
		{
			if (($type=="PDate" || $type=="PDatetime" || $type=="PTime"))
			{
				if ($type=="PDate")
					return "to_timestamp('".$value."','YYYY-MM-DD')";
				else if ($type=="PDatetime")
					return "to_timestamp('".$value."','YYYY-MM-DD HH24:MI:SS')";
				else if ($type=="PTime")
					return "to_timestamp('".$value."','HH24:MI:SS')";
			}
			else
				return parent::filterUpdate($value, $type, $defaultval);
		}
		
		/**
		 * Filter the field for insert
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterInsert($value, $type, $defaultval = null)
		{
			if (($type=="PDate" || $type=="PDatetime" || $type=="PTime"))
			{
				$value = str_replace("-","/",$value);
				
				if ($type=="PDate")
					return "to_timestamp('".$value."','YYYY-MM-DD')";
				else if ($type=="PDatetime")
					return "to_timestamp('".$value."','YYYY-MM-DD HH24:MI:SS')";
				else if ($type=="PTime")
					return "to_timestamp('".$value."','HH24:MI:SS')";
			}
			else
				return parent::filterInsert($value, $type, $defaultval);
		}
		
		/**
		 * Filter the field for select
		 * @param $tablename
		 * @param field
		 * @param $key
		 * @param $type
		 * @return string
		 */
		public function filterSelect($tablename, $field, $key, $type)
		{
			if ($type == "PDate")
				return "to_char(".$tablename.".".$field.", 'YYYY-MM-DD') as ".$key;
			else if ($type == "PDatetime")
				return "to_char(".$tablename.".".$field.", 'YYYY-MM-DD HH24:MI:SS') as ".$key;
			else if ($type == "PTime")
				return "to_char(".$tablename.".".$field.", 'HH24:MI:SS') as ".$key;
			else
				return parent::filterSelect($tablename, $field, $key, $type);
		}
		
		/**
		 * Filter the result
		 * @param $key
		 * @return string
		 */
		public function filterResult($key)
		{
			return strtoupper($key);
		}
		
		/**
		 * Returns the last inserted itme id
		 * @param $pdodb
		 * @param $table
		 * @param $idname
		 * @return integer
		 */
		public function getLastId($pdodb, $table, $idname)
		{
			$seq_name = $table."_".$idname."_seq";
			
			$sql = "select ".$seq_name.".currval as id from dual";
			$rsId = POPDB::getConnection($pdodb)->query($sql);
			
			foreach ($rsId as $row)
			{
				$value = (int)$row[0];
			}

			$rsId->closeCursor();
			
			return $value;
		}
		
		/**
		 * Begin transaction
		 * @param $pdodb
		 * @return void
		 */
		public function beginTransaction($pdodb)
		{
			POPDB::getConnection($pdodb)->beginTransaction();
		}
		
		/**
		 * Commit transaction
		 * @param $pdodb
		 * @return void
		 */
		public function commit($pdodb)
		{
			POPDB::getConnection($pdodb)->commit();
		}
		
		/**
		 * Rollsback transaction
		 * @param $pdodb
		 * @return void
		 */
		public function rollBack($pdodb)
		{
			POPDB::getConnection($pdodb)->rollBack();
		}

		/**
		 * Returns the required string for the createTable method
		 * @return string
		 */
		public function identityField()
		{
			return " integer not null ";
		}
		
		/**
		 * Creates the required schema to avoid errors on creating tables
		 * @param $schema
		 * @return void
		 */
		public function createSchema($pdodb, $schema)
		{
			//Oracle is quite complex for schema creation
			POPDebug::log("REMEMBER: to avoid errors you must create your schemas manually before.");
		}

		/**
		 * Returns the required SQL for sequences
		 * @param $table
		 * @return void
		 */
		public function sequenceCreation($table)
		{
			$sqlid  = "\ncreate sequence ".$table."_id_seq\n";
			$sqlid .= "minvalue 1\n";
			$sqlid .= "maxvalue 999999999999999999\n";
			$sqlid .= "start with 1\n";
			$sqlid .= "increment by 1\n";
			$sqlid .= "nocache\n";

			$sqlid .= "\ncreate trigger ".$table."_id_seq_t\n";
			$sqlid .= "before insert on ".$table."\n";
			$sqlid .= "for each row\n";
			$sqlid .= "begin \n";
			$sqlid .= "if :new.id is null then\n";
			$sqlid .= "select ".$table."_id_seq.nextval into :new.id from dual;\n";
			$sqlid .= "end if;\n";
			$sqlid .= "end;\n";
			$sqlid .= "\n";
			
			return $sqlid;
		}

		/**
		 * Returns the required SQL for field creation
		 * @param $type
		 * @param $defaultval
		 * @param $size
		 * @return void
		 */
		public function fieldCreation($type, $defaultval, $size=null)
		{
			if ($type=="datetime" || $type=="time")
				$sql_field = " timestamp ";
			else if ($type=="text")
				$sql_field = " clob ";
			else
				$sql_field = " ".$type." ";

			if ($type=="varchar")
				$sql_field .= "(".$size.") ";

			if (is_null($defaultval))
				$sql_field .= " default null";
			else
			{
				if ($defaultval && $type!="datetime" && $type!="date" && $type!="time" && $type!="varchar" && $type!="text")
					$sql_field .= " default ".$defaultval;
			}
			
			return $sql_field;
		}
		
		/**
		 * Returns the required SQL for propagation
		 * @return void
		 */
		public function createPropagation()
		{
			$sql = "\n\t\ton delete cascade";
			
			return $sql;
		}

		/**
		 * Alter table
		 * @return void
		 */
		public function alterTable()
		{
		}

		/**
		 * Drops a table
		 * @param $pdodb
		 * @param $schema
		 * @param $table
		 * @return void
		 */
		public function dropTable($pdodb, $schema, $table)
		{
			$sql  = "select count(1) from all_tables where table_name = '".strtoupper($table)."'";
			$rs = POPDB::getConnection($pdodb)->query($sql);
			$result = $rs->fetchAll();
			$rs->closeCursor();
			POPDebug::log($sql);
			foreach ($result as $row)
				if($row[0])
				{
					if ($this->pop_parent_type == "Persist")
					{
						$sql  = "SELECT COUNT(1) FROM all_triggers where trigger_name = '".strtoupper($table)."_ID_SEQ_T'";
						$rst = POPDB::getConnection($pdodb)->query($sql);
						$result_t = $rst->fetchAll();
						$rst->closeCursor();
						POPDebug::log($sql);
						foreach ($result_t as $row_t)
						{
							if($row_t[0])
							{
								$sql = "drop trigger ".$table."_id_seq_t\n";
								POPDB::getConnection($pdodb)->exec($sql);
								POPDebug::log($sql);
							}
						}

						$sql  = "SELECT COUNT(1) FROM all_sequences where sequence_name = '".strtoupper($table)."_ID_SEQ'";
						$rss = POPDB::getConnection($pdodb)->query($sql);
						$result_s = $rst->fetchAll();
						$rss->closeCursor();
						POPDebug::log($sql);
						foreach ($result_s as $row_s)
						{
							if($row_s[0])
							{
								$sql = "drop sequence ".$table."_id_seq\n";
								POPDB::getConnection($pdodb)->exec($sql);
								POPDebug::log($sql);
							}
						}
					}
					$sql = "drop table ".$table."\n";
					POPDB::getConnection($pdodb)->exec($sql);
					POPDebug::log($sql);
				}
			$sql = "";
		}
	}
	
	/** 
	 * Register the class for the driver
	 */
	POPDBDriverRegistry::addDriver("oci", "POCIDBDriver");

?>